Assignment: SQL Notebook for Peer Assignment
Estimated time needed: 60 minutes.
Introduction ΒΆ
Using this Python notebook you will:
- Understand the Spacex DataSet
- Load the dataset into the corresponding table in a Db2 database
- Execute SQL queries to answer assignment questions
Overview of the DataSet ΒΆ
SpaceX has gained worldwide attention for a series of historic milestones.
It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010. SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage.
Therefore if we can determine if the first stage will land, we can determine the cost of a launch.
This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.
This dataset includes a record for each payload carried during a SpaceX mission into outer space.
Download the datasets ΒΆ
This assignment requires you to load the spacex dataset.
In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):
Store the dataset in database table ΒΆ
it is highly recommended to manually load the table using the database console LOAD tool in DB2 .
Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new table as follows:
SPACEXDATASET
Follow these steps while using old DB2 UI which is having Open Console Screen
Note:While loading Spacex dataset, ensure that detect datatypes is disabled. Later click on the pencil icon(edit option).
-
Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\:MM:SS.
Here you should place the cursor at Date field and manually type as DD-MM-YYYY.
-
Change the PAYLOAD MASS\ _KG_ datatype to INTEGER.
Changes to be considered when having DB2 instance with the new UI having Go to UI screen
-
Refer to this insruction in this link for viewing the new Go to UI screen.
-
Later click on Data link(below SQL) in the Go to UI screen and click on Load Data tab.
-
Later browse for the downloaded spacex file.
-
Once done select the schema andload the file.
!pip install sqlalchemy==1.3.9
!pip install ibm_db_sa
!pip install ipython-sql
Connect to the database ΒΆ
Let us first load the SQL extension and establish a connection with the database
%load_ext sql
DB2 magic in case of old UI service credentials.
In the next cell enter your db2 connection string. Recall you created Service Credentials for your Db2 instance before. From the uri field of your Db2 service credentials copy everything after db2:// (except the double quote at the end) and paste it in the cell below after ibm_db_sa://
in the following format
%sql ibm_db_sa://my-username:my-password\@my-hostname:my-port/my-db-name
DB2 magic in case of new UI service credentials.
-
Use the following format.
-
Add security=SSL at the end
%sql ibm_db_sa://my-username:my-password\@my-hostname:my-port/my-db-name?security=SSL
## CONNECTION:
# %sql ibm_db_sa://USER:PASSWORD@764264db-9824-4b7c-82df-40d1b13897c2.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32536/bludb?authSource=admin&replicaSet=replset&security=SSL
from secret import sql_user, sql_pass
%sql ibm_db_sa://{sql_user}:{sql_pass}@764264db-9824-4b7c-82df-40d1b13897c2.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32536/bludb?authSource=admin&replicaSet=replset&security=SSL
%%sql
SELECT DISTINCT launch_site FROM SPACEXTBL;
%%sql
SELECT * FROM SPACEXTBL WHERE launch_site LIKE 'CCA%' LIMIT 5;
%%sql
SELECT sum(payload_mass__kg_) AS "Total payload mass (NASA (CRS))" FROM SPACEXTBL WHERE customer = 'NASA (CRS)';
%%sql
SELECT AVG(payload_mass__kg_) AS "Average payload mass (booster version F9 v1.1)" FROM SPACEXTBL WHERE booster_version LIKE 'F9 v1.1%';
%%sql
SELECT min(DATE) AS "First successful landing outcome in ground pad" FROM SPACEXTBL WHERE landing__outcome = 'Success (ground pad)';
%%sql
SELECT booster_version FROM SPACEXTBL WHERE landing__outcome = 'Success (drone ship)' AND payload_mass__kg_ BETWEEN 4000 AND 6000;
%%sql
SELECT 'Success' AS "Outcome", count(*) AS "Count" FROM SPACEXTBL WHERE landing__outcome LIKE 'Success%'
UNION ALL
SELECT 'Failure' AS "Outcome", count(*) AS "Count" FROM SPACEXTBL WHERE landing__outcome NOT LIKE 'Success%'
UNION ALL
SELECT '(All)' AS "Outcome", count(*) AS "Count" FROM SPACEXTBL;
%%sql
SELECT DISTINCT booster_version
FROM SPACEXTBL
WHERE payload_mass__kg_ = (
SELECT max(payload_mass__kg_)
FROM SPACEXTBL
)
%%sql
SELECT booster_version, launch_site,landing__outcome
FROM SPACEXTBL
WHERE landing__outcome = 'Failure (drone ship)'
AND EXTRACT(YEAR FROM DATE) = 2015
%%sql
SELECT landing__outcome, COUNT(*) AS "Count"
FROM SPACEXTBL
WHERE DATE BETWEEN '2010-06-04' and '2017-03-20'
GROUP BY landing__outcome
ORDER BY Count DESC
;
Author(s) ΒΆ
Lakshmi Holla
Other Contributors ΒΆ
Rav Ahuja
Change log ΒΆ
| Date | Version | Changed by | Change Description |
|---|---|---|---|
| 2021-10-12 | 0.4 | Lakshmi Holla | Changed markdown |
| 2021-08-24 | 0.3 | Lakshmi Holla | Added library update |
| 2021-07-09 | 0.2 | Lakshmi Holla | Changes made in magic sql |
| 2021-05-20 | 0.1 | Lakshmi Holla | Created Initial Version |